{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6c591f54",
   "metadata": {},
   "source": [
    "# 统计所有投标的供应商中评分被推荐且排名第一的项目中，超过预算的占比"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c84debd5",
   "metadata": {},
   "source": [
    "## 交叉表 & 透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "fe09d9a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "#读取 projectInfo\n",
    "projectInfo= pd.read_csv('d:/data/bidding/tdz_zbs_project.csv',usecols=['stpr_id','project_code','project_name','bundle_code','project_money','specie_type_name'])\n",
    "projectInfo=projectInfo.set_index(['stpr_id','bundle_code'])\n",
    "# projectInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "376005eb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bidInfo = pd.read_csv('d:/data/bidding/tdz_tbs_open_bid.csv',usecols=['stpr_id','project_name','project_code','bundle_code','supply_name','bid_quote','amount'])\n",
    "bidInfo = bidInfo.set_index(['stpr_id','bundle_code'])\n",
    "# bidInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f6a98c41",
   "metadata": {},
   "outputs": [],
   "source": [
    "bidResult = pd.read_csv('d:/data/bidding/tdz_bsr_score_result.csv',usecols=['stpr_id','bundle_code','supply_name','ranking','flag'])\n",
    "bidResult = bidResult.set_index(['stpr_id','bundle_code'])\n",
    "# bidResult"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "43c7368a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>project_code_x</th>\n",
       "      <th>project_name_x</th>\n",
       "      <th>project_money</th>\n",
       "      <th>specie_type_name</th>\n",
       "      <th>project_name_y</th>\n",
       "      <th>project_code_y</th>\n",
       "      <th>supply_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>bid_quote</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stpr_id</th>\n",
       "      <th>bundle_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">16aebe0a-1bdf-4022-b2bf-0284c1a18423</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州格卢瑞生命科学有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2076800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>深圳市贝克生物科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2079000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州睿贝医学科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2078500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f7e67994-24a7-470c-8c8c-2bb14d4447e9</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>湖南矩阵电子科技有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4212500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>北京航天河科技发展有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4154000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f87ef8ce-8552-4c2f-bb71-9d39309e847b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广州大苗科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1715800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广东省中科进出口有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1711500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">366b7281-a8fd-4a2c-b895-fba8af4f4200</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京实创德激光科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2598000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>宁波宏太科真空科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2600000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京维开科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2595000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9092 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     project_code_x  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                                       project_name_x  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                  project_money  \\\n",
       "stpr_id                              bundle_code                  \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                4275000.0   \n",
       "                                     1                4275000.0   \n",
       "...                                                         ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1720000.0   \n",
       "                                     1                1720000.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "\n",
       "                                                 specie_type_name  \\\n",
       "stpr_id                              bundle_code                    \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "...                                                           ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "\n",
       "                                                                       project_name_y  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                     project_code_y  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                    supply_name  amount  \\\n",
       "stpr_id                              bundle_code                          \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            广州格卢瑞生命科学有限公司     1.0   \n",
       "                                     1            深圳市贝克生物科技有限公司     1.0   \n",
       "                                     1             广州睿贝医学科技有限公司     1.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             湖南矩阵电子科技有限公司    30.0   \n",
       "                                     1            北京航天河科技发展有限公司    30.0   \n",
       "...                                                         ...     ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1               广州大苗科技有限公司     1.0   \n",
       "                                     1             广东省中科进出口有限公司     1.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            北京实创德激光科技有限公司     1.0   \n",
       "                                     1            宁波宏太科真空科技有限公司     1.0   \n",
       "                                     1               北京维开科技有限公司     1.0   \n",
       "\n",
       "                                                  bid_quote  \n",
       "stpr_id                              bundle_code             \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            2076800.0  \n",
       "                                     1            2079000.0  \n",
       "                                     1            2078500.0  \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1            4212500.0  \n",
       "                                     1            4154000.0  \n",
       "...                                                     ...  \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            1715800.0  \n",
       "                                     1            1711500.0  \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            2598000.0  \n",
       "                                     1            2600000.0  \n",
       "                                     1            2595000.0  \n",
       "\n",
       "[9092 rows x 9 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把 project 和投标表合并\n",
    "bidProject = pd.merge(projectInfo,bidInfo,on=['stpr_id','bundle_code'],how='left')\n",
    "bidProject"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "61aa98bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>project_code_x</th>\n",
       "      <th>project_name_x</th>\n",
       "      <th>project_money</th>\n",
       "      <th>specie_type_name</th>\n",
       "      <th>project_name_y</th>\n",
       "      <th>project_code_y</th>\n",
       "      <th>supply_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>bid_quote</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stpr_id</th>\n",
       "      <th>bundle_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">16aebe0a-1bdf-4022-b2bf-0284c1a18423</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州格卢瑞生命科学有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2076800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>深圳市贝克生物科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2079000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州睿贝医学科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2078500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f7e67994-24a7-470c-8c8c-2bb14d4447e9</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>湖南矩阵电子科技有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4212500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>北京航天河科技发展有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4154000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f87ef8ce-8552-4c2f-bb71-9d39309e847b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广州大苗科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1715800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广东省中科进出口有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1711500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">366b7281-a8fd-4a2c-b895-fba8af4f4200</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京实创德激光科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2598000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>宁波宏太科真空科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2600000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京维开科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2595000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7991 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     project_code_x  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                                       project_name_x  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                  project_money  \\\n",
       "stpr_id                              bundle_code                  \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                4275000.0   \n",
       "                                     1                4275000.0   \n",
       "...                                                         ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1720000.0   \n",
       "                                     1                1720000.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "\n",
       "                                                 specie_type_name  \\\n",
       "stpr_id                              bundle_code                    \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "...                                                           ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "\n",
       "                                                                       project_name_y  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                     project_code_y  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                    supply_name  amount  \\\n",
       "stpr_id                              bundle_code                          \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            广州格卢瑞生命科学有限公司     1.0   \n",
       "                                     1            深圳市贝克生物科技有限公司     1.0   \n",
       "                                     1             广州睿贝医学科技有限公司     1.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             湖南矩阵电子科技有限公司    30.0   \n",
       "                                     1            北京航天河科技发展有限公司    30.0   \n",
       "...                                                         ...     ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1               广州大苗科技有限公司     1.0   \n",
       "                                     1             广东省中科进出口有限公司     1.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            北京实创德激光科技有限公司     1.0   \n",
       "                                     1            宁波宏太科真空科技有限公司     1.0   \n",
       "                                     1               北京维开科技有限公司     1.0   \n",
       "\n",
       "                                                  bid_quote  \n",
       "stpr_id                              bundle_code             \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            2076800.0  \n",
       "                                     1            2079000.0  \n",
       "                                     1            2078500.0  \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1            4212500.0  \n",
       "                                     1            4154000.0  \n",
       "...                                                     ...  \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            1715800.0  \n",
       "                                     1            1711500.0  \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            2598000.0  \n",
       "                                     1            2600000.0  \n",
       "                                     1            2595000.0  \n",
       "\n",
       "[7991 rows x 9 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp = bidProject.query('bid_quote>0')\n",
    "tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "954861e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_11164\\1976805506.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  tmp['outRank']= round(p,2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>project_code_x</th>\n",
       "      <th>project_name_x</th>\n",
       "      <th>project_money</th>\n",
       "      <th>specie_type_name</th>\n",
       "      <th>project_name_y</th>\n",
       "      <th>project_code_y</th>\n",
       "      <th>supply_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>bid_quote</th>\n",
       "      <th>outRank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stpr_id</th>\n",
       "      <th>bundle_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">16aebe0a-1bdf-4022-b2bf-0284c1a18423</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州格卢瑞生命科学有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2076800.0</td>\n",
       "      <td>-0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>深圳市贝克生物科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2079000.0</td>\n",
       "      <td>-0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州睿贝医学科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2078500.0</td>\n",
       "      <td>-0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f7e67994-24a7-470c-8c8c-2bb14d4447e9</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>湖南矩阵电子科技有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4212500.0</td>\n",
       "      <td>-1.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>北京航天河科技发展有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4154000.0</td>\n",
       "      <td>-2.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f87ef8ce-8552-4c2f-bb71-9d39309e847b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广州大苗科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1715800.0</td>\n",
       "      <td>-0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广东省中科进出口有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1711500.0</td>\n",
       "      <td>-0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">366b7281-a8fd-4a2c-b895-fba8af4f4200</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京实创德激光科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2598000.0</td>\n",
       "      <td>-0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>宁波宏太科真空科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京维开科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2595000.0</td>\n",
       "      <td>-0.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7991 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     project_code_x  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                                       project_name_x  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                  project_money  \\\n",
       "stpr_id                              bundle_code                  \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                4275000.0   \n",
       "                                     1                4275000.0   \n",
       "...                                                         ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1720000.0   \n",
       "                                     1                1720000.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "\n",
       "                                                 specie_type_name  \\\n",
       "stpr_id                              bundle_code                    \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "...                                                           ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "\n",
       "                                                                       project_name_y  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                     project_code_y  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                    supply_name  amount  \\\n",
       "stpr_id                              bundle_code                          \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            广州格卢瑞生命科学有限公司     1.0   \n",
       "                                     1            深圳市贝克生物科技有限公司     1.0   \n",
       "                                     1             广州睿贝医学科技有限公司     1.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             湖南矩阵电子科技有限公司    30.0   \n",
       "                                     1            北京航天河科技发展有限公司    30.0   \n",
       "...                                                         ...     ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1               广州大苗科技有限公司     1.0   \n",
       "                                     1             广东省中科进出口有限公司     1.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            北京实创德激光科技有限公司     1.0   \n",
       "                                     1            宁波宏太科真空科技有限公司     1.0   \n",
       "                                     1               北京维开科技有限公司     1.0   \n",
       "\n",
       "                                                  bid_quote  outRank  \n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            2076800.0    -0.15  \n",
       "                                     1            2079000.0    -0.05  \n",
       "                                     1            2078500.0    -0.07  \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1            4212500.0    -1.46  \n",
       "                                     1            4154000.0    -2.83  \n",
       "...                                                     ...      ...  \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            1715800.0    -0.24  \n",
       "                                     1            1711500.0    -0.49  \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            2598000.0    -0.08  \n",
       "                                     1            2600000.0     0.00  \n",
       "                                     1            2595000.0    -0.19  \n",
       "\n",
       "[7991 rows x 10 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add()  sub()  mul()  div()\n",
    "#   +      -      *      /\n",
    "\n",
    "p = tmp['bid_quote'].sub(tmp['project_money']).div(tmp['project_money']).mul(100)\n",
    "\n",
    "tmp['outRank']= round(p,2)\n",
    "tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "32a89897",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amount\n"
     ]
    }
   ],
   "source": [
    "# np.any(pd.isnull(tmp))\n",
    "# 打印有空值的列\n",
    "for i in tmp.columns:\n",
    "    if np.any(pd.isnull(tmp[i])) ==True:\n",
    "        print(i)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3775cc24",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_11164\\334202854.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  tmp['amount'].fillna(-1, inplace=True)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>project_code_x</th>\n",
       "      <th>project_name_x</th>\n",
       "      <th>project_money</th>\n",
       "      <th>specie_type_name</th>\n",
       "      <th>project_name_y</th>\n",
       "      <th>project_code_y</th>\n",
       "      <th>supply_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>bid_quote</th>\n",
       "      <th>outRank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stpr_id</th>\n",
       "      <th>bundle_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">16aebe0a-1bdf-4022-b2bf-0284c1a18423</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州格卢瑞生命科学有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2076800.0</td>\n",
       "      <td>-0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>深圳市贝克生物科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2079000.0</td>\n",
       "      <td>-0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州睿贝医学科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2078500.0</td>\n",
       "      <td>-0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f7e67994-24a7-470c-8c8c-2bb14d4447e9</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>湖南矩阵电子科技有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4212500.0</td>\n",
       "      <td>-1.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>北京航天河科技发展有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4154000.0</td>\n",
       "      <td>-2.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">f87ef8ce-8552-4c2f-bb71-9d39309e847b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广州大苗科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1715800.0</td>\n",
       "      <td>-0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广东省中科进出口有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1711500.0</td>\n",
       "      <td>-0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">366b7281-a8fd-4a2c-b895-fba8af4f4200</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京实创德激光科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2598000.0</td>\n",
       "      <td>-0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>宁波宏太科真空科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京维开科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2595000.0</td>\n",
       "      <td>-0.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7991 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     project_code_x  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                                       project_name_x  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                  project_money  \\\n",
       "stpr_id                              bundle_code                  \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "                                     1                2080000.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                4275000.0   \n",
       "                                     1                4275000.0   \n",
       "...                                                         ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1720000.0   \n",
       "                                     1                1720000.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "                                     1                2600000.0   \n",
       "\n",
       "                                                 specie_type_name  \\\n",
       "stpr_id                              bundle_code                    \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "...                                                           ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                        货物类   \n",
       "                                     1                        货物类   \n",
       "                                     1                        货物类   \n",
       "\n",
       "                                                                       project_name_y  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "                                     1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "                                     1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "...                                                                               ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "                                     1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "                                     1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                     project_code_y  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "                                     1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "                                     1             中大招（货）[2022]223号   \n",
       "...                                                             ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "                                     1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "                                     1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                    supply_name  amount  \\\n",
       "stpr_id                              bundle_code                          \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            广州格卢瑞生命科学有限公司     1.0   \n",
       "                                     1            深圳市贝克生物科技有限公司     1.0   \n",
       "                                     1             广州睿贝医学科技有限公司     1.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             湖南矩阵电子科技有限公司    30.0   \n",
       "                                     1            北京航天河科技发展有限公司    30.0   \n",
       "...                                                         ...     ...   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1               广州大苗科技有限公司     1.0   \n",
       "                                     1             广东省中科进出口有限公司     1.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            北京实创德激光科技有限公司     1.0   \n",
       "                                     1            宁波宏太科真空科技有限公司     1.0   \n",
       "                                     1               北京维开科技有限公司     1.0   \n",
       "\n",
       "                                                  bid_quote  outRank  \n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            2076800.0    -0.15  \n",
       "                                     1            2079000.0    -0.05  \n",
       "                                     1            2078500.0    -0.07  \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1            4212500.0    -1.46  \n",
       "                                     1            4154000.0    -2.83  \n",
       "...                                                     ...      ...  \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            1715800.0    -0.24  \n",
       "                                     1            1711500.0    -0.49  \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            2598000.0    -0.08  \n",
       "                                     1            2600000.0     0.00  \n",
       "                                     1            2595000.0    -0.19  \n",
       "\n",
       "[7991 rows x 10 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tmp['good_name'].fillna(' ', inplace=True)\n",
    "# tmp['makeFactory'].fillna(' ', inplace=True)\n",
    "# tmp['spec'].fillna(' ', inplace=True)\n",
    "tmp['amount'].fillna(-1, inplace=True)\n",
    "# tmp.dropna()\n",
    "tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a7a6d1b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.any(pd.isnull(tmp))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f58b6589",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stpr_id                               bundle_code\n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423  1             -0.15\n",
       "                                      1             -0.05\n",
       "                                      1             -0.07\n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9  1             -1.46\n",
       "                                      1             -2.83\n",
       "                                                     ... \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b  1             -0.24\n",
       "                                      1             -0.49\n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200  1             -0.08\n",
       "                                      1              0.00\n",
       "                                      1             -0.19\n",
       "Name: outRank, Length: 7991, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp['outRank']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "6563e641",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>project_code_x</th>\n",
       "      <th>project_name_x</th>\n",
       "      <th>project_money</th>\n",
       "      <th>specie_type_name</th>\n",
       "      <th>project_name_y</th>\n",
       "      <th>project_code_y</th>\n",
       "      <th>supply_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>bid_quote</th>\n",
       "      <th>outRank</th>\n",
       "      <th>ranking</th>\n",
       "      <th>flag</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stpr_id</th>\n",
       "      <th>bundle_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16aebe0a-1bdf-4022-b2bf-0284c1a18423</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州睿贝医学科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2078500.00</td>\n",
       "      <td>-0.07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f7e67994-24a7-470c-8c8c-2bb14d4447e9</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>北京航天河科技发展有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4154000.00</td>\n",
       "      <td>-2.83</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dcca20ed-4aea-4b4f-befe-188f226945c7</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1045号</td>\n",
       "      <td>中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目</td>\n",
       "      <td>6620000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目</td>\n",
       "      <td>中大招（货）[2022]1045号</td>\n",
       "      <td>深圳市宝应电子有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6597144.00</td>\n",
       "      <td>-0.35</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20153f94-a8ed-4726-ae45-209db635c34b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2023]124号</td>\n",
       "      <td>中山大学广州校区南校园与东校园教学楼空调更换采购项目</td>\n",
       "      <td>2212900.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学广州校区南校园与东校园教学楼空调更换采购项目</td>\n",
       "      <td>中大招（货）[2023]124号</td>\n",
       "      <td>广州汉能制冷技术有限公司</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1367189.00</td>\n",
       "      <td>-38.22</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39a25800-7e53-4183-99ef-74c2c0ab12fa</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1162号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目</td>\n",
       "      <td>14370000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目</td>\n",
       "      <td>中大招（货）[2022]1162号</td>\n",
       "      <td>中国医药保健品有限公司</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14330000.00</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00da2de8-cd86-4b39-973f-589bbb92d6ce</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]927号</td>\n",
       "      <td>中山大学材料学院超高温综合热分析仪采购项目</td>\n",
       "      <td>1850000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学材料学院超高温综合热分析仪采购项目</td>\n",
       "      <td>中大招（货）[2022]927号</td>\n",
       "      <td>广州市晞灵国际贸易有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1849600.00</td>\n",
       "      <td>-0.02</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>afa4fc62-ba82-40f7-b314-8b3a11f4693e</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1346号</td>\n",
       "      <td>中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目</td>\n",
       "      <td>2500000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1346号</td>\n",
       "      <td>广州广电国际商贸有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2498000.00</td>\n",
       "      <td>-0.08</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0e6bdb27-3de0-464f-a26e-44e251bc20b1</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]162号</td>\n",
       "      <td>中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目</td>\n",
       "      <td>1477013.8</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目</td>\n",
       "      <td>中大招（货）[2022]162号</td>\n",
       "      <td>广州诚源科技发展有限公司</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1128777.56</td>\n",
       "      <td>-23.58</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f87ef8ce-8552-4c2f-bb71-9d39309e847b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广东省中科进出口有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1711500.00</td>\n",
       "      <td>-0.49</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366b7281-a8fd-4a2c-b895-fba8af4f4200</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京维开科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2595000.00</td>\n",
       "      <td>-0.19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1016 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     project_code_x  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1            中大招（货）[2022]1045号   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             中大招（货）[2023]124号   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1            中大招（货）[2022]1162号   \n",
       "...                                                             ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1             中大招（货）[2022]927号   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1            中大招（货）[2022]1346号   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             中大招（货）[2022]162号   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                                       project_name_x  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1              中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                     中山大学广州校区南校园与东校园教学楼空调更换采购项目   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1               中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目   \n",
       "...                                                                               ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                          中山大学材料学院超高温综合热分析仪采购项目   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                 中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                 中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                  project_money  \\\n",
       "stpr_id                              bundle_code                  \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                2080000.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                4275000.0   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1                6620000.0   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                2212900.0   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1               14370000.0   \n",
       "...                                                         ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                1850000.0   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                2500000.0   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                1477013.8   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1720000.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                2600000.0   \n",
       "\n",
       "                                                 specie_type_name  \\\n",
       "stpr_id                              bundle_code                    \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                        货物类   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                        货物类   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1                        货物类   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                        货物类   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1                        货物类   \n",
       "...                                                           ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                        货物类   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                        货物类   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                        货物类   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                        货物类   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                        货物类   \n",
       "\n",
       "                                                                       project_name_y  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1              中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                     中山大学广州校区南校园与东校园教学楼空调更换采购项目   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1               中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目   \n",
       "...                                                                               ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                          中山大学材料学院超高温综合热分析仪采购项目   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                 中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                 中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                     project_code_y  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1            中大招（货）[2022]1045号   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             中大招（货）[2023]124号   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1            中大招（货）[2022]1162号   \n",
       "...                                                             ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1             中大招（货）[2022]927号   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1            中大招（货）[2022]1346号   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             中大招（货）[2022]162号   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                    supply_name  amount  \\\n",
       "stpr_id                              bundle_code                          \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1             广州睿贝医学科技有限公司     1.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1            北京航天河科技发展有限公司    30.0   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1              深圳市宝应电子有限公司     1.0   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             广州汉能制冷技术有限公司     8.0   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1              中国医药保健品有限公司     4.0   \n",
       "...                                                         ...     ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1            广州市晞灵国际贸易有限公司     1.0   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1             广州广电国际商贸有限公司     1.0   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             广州诚源科技发展有限公司     9.0   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1             广东省中科进出口有限公司     1.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1               北京维开科技有限公司     1.0   \n",
       "\n",
       "                                                    bid_quote  outRank  \\\n",
       "stpr_id                              bundle_code                         \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1             2078500.00    -0.07   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             4154000.00    -2.83   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1             6597144.00    -0.35   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             1367189.00   -38.22   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1            14330000.00    -0.28   \n",
       "...                                                       ...      ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1             1849600.00    -0.02   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1             2498000.00    -0.08   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             1128777.56   -23.58   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1             1711500.00    -0.49   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             2595000.00    -0.19   \n",
       "\n",
       "                                                  ranking  flag  \n",
       "stpr_id                              bundle_code                 \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                1.0   1.0  \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                1.0   1.0  \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1                1.0   1.0  \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                1.0   1.0  \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1                1.0   1.0  \n",
       "...                                                   ...   ...  \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                1.0   1.0  \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                1.0   1.0  \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                1.0   1.0  \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1.0   1.0  \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                1.0   1.0  \n",
       "\n",
       "[1016 rows x 12 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 合并评分表\n",
    "\n",
    "result = pd.merge(tmp,bidResult,on=['stpr_id','bundle_code','supply_name'],how='left')\n",
    "\n",
    "# bidProject.query('project_money>bid_quote')\n",
    "result = result.query('ranking==1 & flag==1')\n",
    "\n",
    "# 去重，因为在评分时，多位专家对同一个供应商评分排名相同时，会出现重复\n",
    "result = result.drop_duplicates()\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "54f407ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 只有一维数组才能切片，数据需要降维\n",
    "\n",
    "# level = [-100,-50,-20,-15,-10,-5,-4,-3,-2,-1,-0.8,-0.6,-0.4,-0.2,0,10,50]\n",
    "# pd.cut(result['outRank'],level).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f5c7d95",
   "metadata": {},
   "source": [
    "### 交叉表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c65d9b09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>project_code_x</th>\n",
       "      <th>project_name_x</th>\n",
       "      <th>project_money</th>\n",
       "      <th>specie_type_name</th>\n",
       "      <th>project_name_y</th>\n",
       "      <th>project_code_y</th>\n",
       "      <th>supply_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>bid_quote</th>\n",
       "      <th>outRank</th>\n",
       "      <th>ranking</th>\n",
       "      <th>flag</th>\n",
       "      <th>cb</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stpr_id</th>\n",
       "      <th>bundle_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16aebe0a-1bdf-4022-b2bf-0284c1a18423</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>2080000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学医学院数字病理玻片扫描仪（荧光）采购项目</td>\n",
       "      <td>中大招（货）[2022]1092号</td>\n",
       "      <td>广州睿贝医学科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2078500.00</td>\n",
       "      <td>-0.07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f7e67994-24a7-470c-8c8c-2bb14d4447e9</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>4275000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目</td>\n",
       "      <td>中大招（货）[2022]223号</td>\n",
       "      <td>北京航天河科技发展有限公司</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4154000.00</td>\n",
       "      <td>-2.83</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dcca20ed-4aea-4b4f-befe-188f226945c7</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1045号</td>\n",
       "      <td>中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目</td>\n",
       "      <td>6620000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目</td>\n",
       "      <td>中大招（货）[2022]1045号</td>\n",
       "      <td>深圳市宝应电子有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6597144.00</td>\n",
       "      <td>-0.35</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20153f94-a8ed-4726-ae45-209db635c34b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2023]124号</td>\n",
       "      <td>中山大学广州校区南校园与东校园教学楼空调更换采购项目</td>\n",
       "      <td>2212900.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学广州校区南校园与东校园教学楼空调更换采购项目</td>\n",
       "      <td>中大招（货）[2023]124号</td>\n",
       "      <td>广州汉能制冷技术有限公司</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1367189.00</td>\n",
       "      <td>-38.22</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39a25800-7e53-4183-99ef-74c2c0ab12fa</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1162号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目</td>\n",
       "      <td>14370000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目</td>\n",
       "      <td>中大招（货）[2022]1162号</td>\n",
       "      <td>中国医药保健品有限公司</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14330000.00</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00da2de8-cd86-4b39-973f-589bbb92d6ce</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]927号</td>\n",
       "      <td>中山大学材料学院超高温综合热分析仪采购项目</td>\n",
       "      <td>1850000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学材料学院超高温综合热分析仪采购项目</td>\n",
       "      <td>中大招（货）[2022]927号</td>\n",
       "      <td>广州市晞灵国际贸易有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1849600.00</td>\n",
       "      <td>-0.02</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>afa4fc62-ba82-40f7-b314-8b3a11f4693e</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1346号</td>\n",
       "      <td>中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目</td>\n",
       "      <td>2500000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1346号</td>\n",
       "      <td>广州广电国际商贸有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2498000.00</td>\n",
       "      <td>-0.08</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0e6bdb27-3de0-464f-a26e-44e251bc20b1</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]162号</td>\n",
       "      <td>中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目</td>\n",
       "      <td>1477013.8</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目</td>\n",
       "      <td>中大招（货）[2022]162号</td>\n",
       "      <td>广州诚源科技发展有限公司</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1128777.56</td>\n",
       "      <td>-23.58</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f87ef8ce-8552-4c2f-bb71-9d39309e847b</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>1720000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目</td>\n",
       "      <td>中大招（货）[2022]1276号</td>\n",
       "      <td>广东省中科进出口有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1711500.00</td>\n",
       "      <td>-0.49</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366b7281-a8fd-4a2c-b895-fba8af4f4200</th>\n",
       "      <th>1</th>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>2600000.0</td>\n",
       "      <td>货物类</td>\n",
       "      <td>中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目</td>\n",
       "      <td>中大招（货）[2022]849号</td>\n",
       "      <td>北京维开科技有限公司</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2595000.00</td>\n",
       "      <td>-0.19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1016 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     project_code_x  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1            中大招（货）[2022]1045号   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             中大招（货）[2023]124号   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1            中大招（货）[2022]1162号   \n",
       "...                                                             ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1             中大招（货）[2022]927号   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1            中大招（货）[2022]1346号   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             中大招（货）[2022]162号   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                                       project_name_x  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1              中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                     中山大学广州校区南校园与东校园教学楼空调更换采购项目   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1               中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目   \n",
       "...                                                                               ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                          中山大学材料学院超高温综合热分析仪采购项目   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                 中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                 中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                  project_money  \\\n",
       "stpr_id                              bundle_code                  \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                2080000.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                4275000.0   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1                6620000.0   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                2212900.0   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1               14370000.0   \n",
       "...                                                         ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                1850000.0   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                2500000.0   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                1477013.8   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1720000.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                2600000.0   \n",
       "\n",
       "                                                 specie_type_name  \\\n",
       "stpr_id                              bundle_code                    \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                        货物类   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                        货物类   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1                        货物类   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                        货物类   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1                        货物类   \n",
       "...                                                           ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                        货物类   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                        货物类   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                        货物类   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                        货物类   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                        货物类   \n",
       "\n",
       "                                                                       project_name_y  \\\n",
       "stpr_id                              bundle_code                                        \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                       中山大学医学院数字病理玻片扫描仪（荧光）采购项目   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                  中山大学电子与通信工程学院北斗导航创新实践平台设备采购项目   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1              中山大学大气科学学院比较行星学综合观测平台：1米口径望远镜采购项目   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                     中山大学广州校区南校园与东校园教学楼空调更换采购项目   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1               中山大学电子与信息工程学院（微电子学院）光电材料刻蚀设备采购项目   \n",
       "...                                                                               ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                          中山大学材料学院超高温综合热分析仪采购项目   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                 中山大学化学学院超高效液相色谱-三重四极杆质谱联用仪采购项目   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                 中山大学2022年南校园部分学生宿舍热水系统维修更换采购项目   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                   中山大学环境科学与工程学院高功率多激光流式细胞仪采购项目   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1            中山大学电子与信息工程学院（微电子学院）高真空多靶磁控溅射系统采购项目   \n",
       "\n",
       "                                                     project_code_y  \\\n",
       "stpr_id                              bundle_code                      \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1            中大招（货）[2022]1092号   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             中大招（货）[2022]223号   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1            中大招（货）[2022]1045号   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             中大招（货）[2023]124号   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1            中大招（货）[2022]1162号   \n",
       "...                                                             ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1             中大招（货）[2022]927号   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1            中大招（货）[2022]1346号   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             中大招（货）[2022]162号   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1            中大招（货）[2022]1276号   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             中大招（货）[2022]849号   \n",
       "\n",
       "                                                    supply_name  amount  \\\n",
       "stpr_id                              bundle_code                          \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1             广州睿贝医学科技有限公司     1.0   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1            北京航天河科技发展有限公司    30.0   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1              深圳市宝应电子有限公司     1.0   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             广州汉能制冷技术有限公司     8.0   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1              中国医药保健品有限公司     4.0   \n",
       "...                                                         ...     ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1            广州市晞灵国际贸易有限公司     1.0   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1             广州广电国际商贸有限公司     1.0   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             广州诚源科技发展有限公司     9.0   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1             广东省中科进出口有限公司     1.0   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1               北京维开科技有限公司     1.0   \n",
       "\n",
       "                                                    bid_quote  outRank  \\\n",
       "stpr_id                              bundle_code                         \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1             2078500.00    -0.07   \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1             4154000.00    -2.83   \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1             6597144.00    -0.35   \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1             1367189.00   -38.22   \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1            14330000.00    -0.28   \n",
       "...                                                       ...      ...   \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1             1849600.00    -0.02   \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1             2498000.00    -0.08   \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1             1128777.56   -23.58   \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1             1711500.00    -0.49   \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1             2595000.00    -0.19   \n",
       "\n",
       "                                                  ranking  flag  cb  \n",
       "stpr_id                              bundle_code                     \n",
       "16aebe0a-1bdf-4022-b2bf-0284c1a18423 1                1.0   1.0   0  \n",
       "f7e67994-24a7-470c-8c8c-2bb14d4447e9 1                1.0   1.0   0  \n",
       "dcca20ed-4aea-4b4f-befe-188f226945c7 1                1.0   1.0   0  \n",
       "20153f94-a8ed-4726-ae45-209db635c34b 1                1.0   1.0   0  \n",
       "39a25800-7e53-4183-99ef-74c2c0ab12fa 1                1.0   1.0   0  \n",
       "...                                                   ...   ...  ..  \n",
       "00da2de8-cd86-4b39-973f-589bbb92d6ce 1                1.0   1.0   0  \n",
       "afa4fc62-ba82-40f7-b314-8b3a11f4693e 1                1.0   1.0   0  \n",
       "0e6bdb27-3de0-464f-a26e-44e251bc20b1 1                1.0   1.0   0  \n",
       "f87ef8ce-8552-4c2f-bb71-9d39309e847b 1                1.0   1.0   0  \n",
       "366b7281-a8fd-4a2c-b895-fba8af4f4200 1                1.0   1.0   0  \n",
       "\n",
       "[1016 rows x 13 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 增加一列，如果当投标价格低于预算，标识【0】，相反标识【1】，方便做统计\n",
    "result['cb']=np.where(result['outRank']<0,0,1)\n",
    "\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "67d1cba7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>project_code_x</th>\n",
       "      <th>project_name_x</th>\n",
       "      <th>project_money</th>\n",
       "      <th>specie_type_name</th>\n",
       "      <th>project_name_y</th>\n",
       "      <th>project_code_y</th>\n",
       "      <th>supply_name</th>\n",
       "      <th>amount</th>\n",
       "      <th>bid_quote</th>\n",
       "      <th>outRank</th>\n",
       "      <th>ranking</th>\n",
       "      <th>flag</th>\n",
       "      <th>cb</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stpr_id</th>\n",
       "      <th>bundle_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1dfe6b20-c002-47d2-816a-e5008cf61911</th>\n",
       "      <th>1</th>\n",
       "      <td>遴选项目测试20230206001</td>\n",
       "      <td>【勿投】测试项目遴选项目测试20230206001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>服务类</td>\n",
       "      <td>【勿投】测试项目遴选项目测试20230206001</td>\n",
       "      <td>遴选项目测试20230206001</td>\n",
       "      <td>测试供应商</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>2.101</td>\n",
       "      <td>inf</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     project_code_x  \\\n",
       "stpr_id                              bundle_code                      \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1            遴选项目测试20230206001   \n",
       "\n",
       "                                                             project_name_x  \\\n",
       "stpr_id                              bundle_code                              \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1            【勿投】测试项目遴选项目测试20230206001   \n",
       "\n",
       "                                                  project_money  \\\n",
       "stpr_id                              bundle_code                  \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1                      0.0   \n",
       "\n",
       "                                                 specie_type_name  \\\n",
       "stpr_id                              bundle_code                    \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1                        服务类   \n",
       "\n",
       "                                                             project_name_y  \\\n",
       "stpr_id                              bundle_code                              \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1            【勿投】测试项目遴选项目测试20230206001   \n",
       "\n",
       "                                                     project_code_y  \\\n",
       "stpr_id                              bundle_code                      \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1            遴选项目测试20230206001   \n",
       "\n",
       "                                                 supply_name  amount  \\\n",
       "stpr_id                              bundle_code                       \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1                 测试供应商    -1.0   \n",
       "\n",
       "                                                  bid_quote  outRank  ranking  \\\n",
       "stpr_id                              bundle_code                                \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1                2.101      inf      1.0   \n",
       "\n",
       "                                                  flag  cb  \n",
       "stpr_id                              bundle_code            \n",
       "1dfe6b20-c002-47d2-816a-e5008cf61911 1             1.0   1  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.query('outRank>0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0fb261a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 业务问题 没想到怎么用交叉表和透视表 \n",
    "# count = pd.crosstab(tab['column1'],tab['column2'])\n",
    "\n",
    "# tab.pivot_table(['column1'],index='column2')\n",
    "\n",
    "\n",
    "\n",
    "# result['rankGroup']=np.where(result['outRank']>-1,1,(result['outRank']>-2,2,(result['outRank']>-5,)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "627ffe7e",
   "metadata": {},
   "source": [
    "## 聚合函数 groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4b0f3d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# table 按照 [column1] 分组\n",
    "\n",
    "# 方法一：默认as_index=true，作为新的索引，即删除旧的索引 \n",
    "table.groupby(['column1'],as_index=True)['column2'].max() #分组后，按组统计最大值\n",
    "\n",
    "# 方法二\n",
    "table['column2'].groupby(table['column1']).mean() #分组后，按组统计平均值"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
